from flask import Flask, request, jsonify
import mysql.connector
from mysql.connector import Error
import json

app = Flask(__name__)


def create_connection():
    """ 创建与MySQL数据库的连接 """
    connection = None
    try:
        connection = mysql.connector.connect(
            host='localhost',  # 数据库主机地址
            user='root',  # 数据库用户名
            passwd='123456',  # 数据库密码
            database='harmony'  # 数据库名称
        )
        if connection.is_connected():
            print('成功连接到数据库')
    except Error as e:
        print(f"错误: {e}")
    return connection


#登陆
@app.route('/data', methods=['GET'])
def get_data_login():
    """ 从数据库中获取数据 """
    table_name = request.args.get('table_name')
    if not table_name:
        return jsonify({"error": "缺少参数 table_name"}), 400

    if not table_name.isidentifier():
        return jsonify({"error": "非法的表名"}), 400

    connection = create_connection()
    cursor = connection.cursor(dictionary=True)
    account = request.args.get('account')
    passwd = request.args.get('passwd')
    query = "SELECT * FROM User_base where AUTOid= %s AND Passwd= %s"
    cursor.execute(query, (account, passwd))
    rows = cursor.fetchall()
    if rows:
        print(rows)
    cursor.close()
    connection.close()
    return jsonify(rows)

#改密码
@app.route('/data/NewPasswd', methods=['POST'])
def post_NewPasswd():
    data = request.get_json()
    if not data:
        return "Invalid JSON payload", 400
    connection = create_connection()
    cursor = connection.cursor()
    account=data.get('account')
    passwd=data.get('passwd')
    print(account)
    print(passwd)

    try:
        query = """
                UPDATE User_base SET passwd=%s WHERE AUTOid=%s
            """
        cursor.execute(query, (passwd,account))
        connection.commit()
        return jsonify({"message": "数据更新成功"}), 201

    except Exception as e:
        # 发生错误时回滚事务
        connection.rollback()
        # 返回错误信息给客户端
        return jsonify({"error": str(e)}), 500

    finally:
        cursor.close()
        connection.close()



#获取设备信息
@app.route('/data/device', methods=['GET'])
def get_data_device():
    connection = create_connection()
    cursor = connection.cursor(dictionary=True)
    query = ("SELECT name,id,labLocation,status,purchaseDate,maintainCycle,"
             "lastMaintainDate,manager,description FROM Device_base")
    cursor.execute(query)
    rows = cursor.fetchall()
    cursor.close()
    connection.close()
    return jsonify(rows)


#获取设备借用信息
@app.route('/data/deviceReserve', methods=['GET'])
def get_data_deviceReserve():
    connection = create_connection()
    cursor = connection.cursor(dictionary=True)
    query = "SELECT * FROM UseDevice_base"
    cursor.execute(query)
    rows = cursor.fetchall()
    print(rows)
    cursor.close()
    connection.close()
    return jsonify(rows)


#获取使用者的实验室信息
@app.route('/data/UserLab', methods=['GET'])
def get_data_UserLab():
    connection = create_connection()
    cursor = connection.cursor(dictionary=True)
    inLabid = request.args.get('inLabid')
    query = "SELECT * FROM Lab_base where inLabid=%d"
    cursor.execute(query, inLabid)
    rows = cursor.fetchall()
    cursor.close()
    connection.close()
    return jsonify(rows)


#获取项目信息
@app.route('/data/ProgramInfo', methods=['GET'])
def get_data_ProgramInfo():
    connection = create_connection()
    cursor = connection.cursor(dictionary=True)
    query = ("SELECT img,title,count,lx,name,text,state,Time,tgTime,jxTime,"
             "(SELECT JSON_ARRAY(lable1,lable2,lable3,lable4) from programbq where "
             "title=Program_base.title) AS bq "
             "FROM Program_base;")
    cursor.execute(query)
    rows = cursor.fetchall()
    for row in rows:
        if row['bq']:
            row['bq'] = json.loads(row['bq'])

    cursor.close()
    connection.close()
    return jsonify(rows)


#设备传输函数 测试中
@app.route('/data/DeviceInfo', methods=['POST'])
def post_device_info():
    data = request.get_json()
    if not data:
        return "Invalid JSON payload", 400
    connection = create_connection()
    cursor = connection.cursor()
    userId = data.get('userId')
    DeviceId = data.get('deviceId')
    Status = data.get('status')
    deviceName = data.get('deviceName')
    startTime = data.get('startTime')
    endTime = data.get('endTime')
    purpose = data.get('purpose')
    approvalStatus=data.get('approvalStatus')
    reasons=data.get('reasons')
    print(data)
    print(reasons)
    try:
        queryD="""
               DELETE FROM usedevice_base where deviceId=%s
        """
        cursor.execute(queryD,(DeviceId,))

        query = """
                INSERT INTO usedevice_base
                (userId,deviceId,status,startTime,endTime,purpose,DeviceName,approvalStatus,reasons)
                VALUES (%s, %s, %s, %s, %s, %s, %s,%s,%s)
            """
        print(query)
        cursor.execute(query, (userId, DeviceId, Status, startTime, endTime, purpose, deviceName,approvalStatus,reasons))

        connection.commit()
        return jsonify({"message": "数据插入成功"}), 201

    except Exception as e:
        # 发生错误时回滚事务
        connection.rollback()
        # 返回错误信息给客户端
        return jsonify({"error": str(e)}), 500

    finally:
        cursor.close()
        connection.close()


#试剂get函数
@app.route('/data/Material', methods=['GET'])
def get_data_Material():
    connection = create_connection()
    cursor = connection.cursor(dictionary=True)
    query = "SELECT * from usedItem"
    cursor.execute(query)
    rows = cursor.fetchall()

    cursor.close()
    connection.close()
    return jsonify(rows)


#添加项目信息
@app.route('/data/ProgramAdd', methods=['POST'])
def post_Program_info():
    data = request.get_json()
    if not data:
        return "Invalid JSON payload", 400
    connection = create_connection()
    cursor = connection.cursor()
    title = data.get('title')
    count = data.get('count')
    name = data.get('name')
    text = data.get('text')
    img = data.get('img')
    lx = data.get('lx')
    state = data.get('state')
    Time = data.get('Time')
    tgTime = data.get('tgTime')
    jxTime = data.get('jxTime')
    #bq = data.get('bq')
    # 获取 bq 并进行初始化和验证
    bq_raw = data.get('bq', [])  # 默认为空列表
    if not isinstance(bq_raw, list):
        bq_raw = []
    # 确保 bq 长度为4，不足部分用空字符串填充
    bq = (bq_raw + [''] * 4)[:4]
    try:
        query = """
                INSERT INTO program_base 
                (title, count, name, text, img, lx, state, Time, tgTime, jxTime) 
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
        cursor.execute(query, (title, count, name, text, img, lx, state, Time, tgTime, jxTime))

        query2 = """
                INSERT INTO programbq 
                (title, lable1, lable2, lable3, lable4) 
                VALUES (%s, %s, %s, %s, %s)
            """
        cursor.execute(query2, (title, bq[0], bq[1], bq[2], bq[3]))

        connection.commit()
        return jsonify({"message": "数据插入成功"}), 201

    except Exception as e:
        # 发生错误时回滚事务
        connection.rollback()
        # 返回错误信息给客户端
        return jsonify({"error": str(e)}), 500

    finally:
        cursor.close()
        connection.close()


#修改项目信息
@app.route('/data/ProgramChange', methods=['POST'])
def change_Program_info():
    data = request.get_json()
    if not data:
        return "Invalid JSON payload", 400
    connection = create_connection()
    cursor = connection.cursor()
    title = data.get('title')
    count = data.get('count')
    name = data.get('name')
    text = data.get('text')
    img = data.get('img')
    lx = data.get('lx')
    state = data.get('state')
    Time = data.get('Time')
    tgTime = data.get('tgTime')
    jxTime = data.get('jxTime')
    bq_raw = data.get('bq', [])  # 默认为空列表
    if not isinstance(bq_raw, list):
        bq_raw = []
    # 确保 bq 长度为4，不足部分用空字符串填充
    bq = (bq_raw + [''] * 4)[:4]
    query1 = ("DELETE from program_base where title=%s")
    cursor.execute(query1, (title,))
    query = ("INSERT INTO program_base (title,count,name,text,img,lx,state,Time,tgTime,"
             "jxTime) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)")
    cursor.execute(query, (title, count, name, text, img, lx, state, Time, tgTime, jxTime))
    query2 = ("INSERT INTO programbq (title,lable1,lable2,lable3,lable4) VALUES"
              " (%s,%s,%s,%s,%s)")
    cursor.execute(query2, (title, bq[0], bq[1], bq[2], bq[3]))
    connection.commit()
    rows = cursor.fetchall()
    cursor.close()
    connection.close()
    if rows:
        return jsonify(rows), 200
    else:
        return jsonify("error", "fail to change"), 400

@app.route('/data/Approval', methods=['POST'])
def post_Approval():
    data = request.get_json()
    if not data:
        return "Invalid JSON payload", 400
    connection = create_connection()
    cursor = connection.cursor()
    account=data.get('account')
    passwd=data.get('passwd')
    print(account)
    print(passwd)

    try:
        query = """
                UPDATE User_base SET passwd=%s WHERE AUTOid=%s
            """
        cursor.execute(query, (passwd,account))
        connection.commit()
        return jsonify({"message": "数据更新成功"}), 201

    except Exception as e:
        # 发生错误时回滚事务
        connection.rollback()
        # 返回错误信息给客户端
        return jsonify({"error": str(e)}), 500

    finally:
        cursor.close()
        connection.close()


@app.route('/data', methods=['POST'])
def add_data():
    data = request.get_json()
    if not data:
        return "Invalid JSON payload", 400
    table_name = data.get('table_name')
    connection = create_connection()
    cursor = connection.cursor()
    if table_name == 'User_base':
        account = data.get('account')
        passwd = data.get('passwd')
        if not account or not passwd:
            return "Missing account or password", 400
        query = "SELECT * FROM User_base where AUTOid= %s AND Passwd= %s"
        cursor.execute(query, (account, passwd))
        result = cursor.fetchone()
        connection.commit()
        cursor.close()
        connection.close()

        if result:
            return jsonify({"message": "登录成功"}), 201
        else:
            return jsonify({"message": "登录失败"}), 201

    #new_data = request.get_json()  # 获取POST请求中的JSON数据
    #connection = create_connection()
    #cursor = connection.cursor()
    #query = "INSERT INTO your_table (column1, column2) VALUES (%s, %s)"  # 替换为你的表名和列名
    #cursor.execute(query, (new_data['column1'], new_data['column2']))
    else:
        # 如果不是 User_base 表，也返回一个明确的响应
        cursor.close()
        connection.close()
        return jsonify({"error": "Unsupported table name"}), 400


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000, debug=True)
